package jdbc.day1.demo2_操作;

import jdbc.Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Test {
    public static void main(String[] args)throws Exception {
        //insert();
        //select();
        System.out.println(selectByName2("s"));
    }
    static void insert()throws Exception{
        Connection con= Util.getConnection();
        Statement st=con.createStatement();
        //增删改都调用该方法,返回的int值代表影响的行数
        int rs=st.executeUpdate("insert into stu (sname,score) values('a1',98)");
        if(rs>0){
            System.out.println("添加成功");
        }
        st.close();
        con.close();
    }

    static void select()throws Exception{
        Connection con= Util.getConnection();
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery("select * from stu");
        while (rs.next()){
            System.out.println(rs.getInt("s_id")+"\t"+rs.getObject(2)+"\t"+rs.getString("sname")+"\t"+rs.getDouble("score"));
        }

        rs.close();
        st.close();
        con.close();
    }
    static List<Stu> selectDao()throws Exception{
        Connection con= Util.getConnection();
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery("select * from stu");
        List<Stu> list=new ArrayList<>();

        while (rs.next()){
            list.add(new Stu(rs.getInt("s_id"),rs.getInt("c_id"),rs.getString("sname"),rs.getDouble("score")));
        }

        rs.close();
        st.close();
        con.close();
        return list;
    }

    static List<Stu> selectByName(String name)throws Exception{
        Connection con= Util.getConnection();
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery("select * from stu where sname like '%"+name+"%'");
        List<Stu> list=new ArrayList<>();

        while (rs.next()){
            list.add(new Stu(rs.getInt("s_id"),rs.getInt("c_id"),rs.getString("sname"),rs.getDouble("score")));
        }

        rs.close();
        st.close();
        con.close();
        return list;
    }
    static List<Stu> selectByName2(String name)throws Exception{
        Connection con= Util.getConnection();

        PreparedStatement st=con.prepareStatement("select * from stu where sname like ? and s_id=?");
        st.setString(1,"%"+name+"%");
        st.setInt(2,1);

        ResultSet rs=st.executeQuery();
        List<Stu> list=new ArrayList<>();

        while (rs.next()){
            list.add(new Stu(rs.getInt("s_id"),rs.getInt("c_id"),rs.getString("sname"),rs.getDouble("score")));
        }

        rs.close();
        st.close();
        con.close();
        return list;
    }

}
